-- -------------------------------------------------
--    Project Name:  平台业务量按日统计
--    File Name:     jms_dm.dm_order_source_sum_dt
--    Job Name:      jms_dm.dm_order_source_sum_dt
--    Description:   平台业务量按日统计
--    Author:        leichao
--    date:          2022-05-16
--    update:        suning 修改平台业务量运单表统计时间范围
-- -------------------------------------------------

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_dm.dm_order_source_sum_dt 
select
     to_date(way.input_time)  as input_date 
    ,manage_code              as manage_code      --管理大区编码
    ,manage_name              as manage_name      --管理大区名称
    ,agent_code               as agent_code       --代理区编码
    ,agent_name               as agent_name       --代理区名称
    ,fran_code                as franchisee_code  --加盟商编码
    ,fran_name                as franchisee_name  --加盟商名称
    ,code                     as network_code     --网点编码
    ,name                     as network_name     --网点名称
    ,provider_id              as provider_id      --省份编码
    ,provider_desc            as provider_desc    --省份名称
    ,city_id                  as city_id          --城市编码
    ,city_desc                as city_desc        --城市名称
    ,area_id                  as area_id          --区县编码
    ,area_desc                as area_desc        --区县名称
    ,max(ordersource_code)    as ordersource_code --平台编码
    ,ordersource_name         as ordersource_name --平台名称
    ,count(1)                 as billCount        --业务量
    ,to_date(way.input_time)  as dt
from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt way
left join jms_dim.dim_network_whole_massage dim 
   on way.pick_network_code = dim.code
where dim.agent_name not like '%测试%'
and way.dt between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
and (to_date(way.input_time) between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}')
group by to_date(way.input_time) 
        ,manage_code     
        ,manage_name     
        ,agent_code      
        ,agent_name      
        ,fran_code 
        ,fran_name 
        ,code    
        ,name    
        ,provider_id     
        ,provider_desc   
        ,city_id         
        ,city_desc       
        ,area_id         
        ,area_desc       
        ,ordersource_name
distribute by dt,pmod(hash(rand()),5);